Mysql – MariaDB Optimization – insert and select

insertmariadbMySQLoptimization

I have 2-3 php files.
I am sending sms to users by sending parameter in these php files. I'm checking the 3rd and 4th table before sending sms so select. If there is an id record, I do not insert. If there is no record with that id, I'm inserting. I check tables 3 and 4 for each insert operation. I can make 500-600 inserts at the same time. And I'm sending a parameter to a php file for each record. So the php file is running 500 times instant.

This data is for testing purposes. It only works 20 times.

I want to optimize my database. Do you have optimization suggestions for table structure or my.cnf file?

My server:

CPU Model: Intel(R) Xeon(R) CPU X3440 @ 2.53GHz
CPU Details: 8 Core (2527 MHz)
Ram: 32 GB
Centos 7

UPDATED:

My.cnf:

[mysqld]

performance_schema = ON
innodb_buffer_pool_size=20GB
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
event_scheduler = ON
max_connections = 500
long_query_time = 3
slow_query_log= 1
skip-name-resolve
query_cache_type = 1
query_cache_limit = 4M
query_cache_min_res_unit = 48k
query_cache_size =300M
tmp_table_size = 1500M 
max_heap_table_size = 1500M


read_buffer_size=256K
read_rnd_buffer_size=512K
join_buffer_size=512K
table_definition_cache=1000

innodb_lru_scan_depth=256

wait_timeout=60
innodb_log_file_size = 2G
innodb_buffer_pool_instances=8
skip-networking
sort_buffer_size=4M
log_error=/var/log/mysql_error.log

Mysqltuner:

    [--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.41-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/localhost.localdomain.err(0B)
[!!] Log file /var/lib/mysql/localhost.localdomain.err doesn't exist
[!!] Log file /var/lib/mysql/localhost.localdomain.err isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 4M (Tables: 51)
[--] Data in InnoDB tables: 38M (Tables: 47)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15d 21h 17m 25s (5M q [4.297 qps], 2M conn, TX: 870M, RX: 594M)
[--] Reads / Writes: 72% / 28%
[--] Binary logging is disabled
[--] Physical Memory     : 31.2G
[--] Max MySQL memory    : 25.2G
[--] Other process memory: 428.6M
[--] Total buffers: 22.0G global + 5.5M per thread (500 max threads)
[--] P_S Max memory usage: 459M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 22.7G (72.54% of installed RAM)
[OK] Maximum possible memory usage: 25.2G (80.59% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (5K/5M)
[OK] Highest usage of available connections: 7% (35/500)
[!!] Aborted connections: 17.28%  (438978/2540869)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 17.5% (195K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
[!!] Joins performed without indexes: 147503
[OK] Temporary tables created on disk: 6% (18K on disk / 299K total)
[OK] Thread cache hit rate: 80% (503K created / 2M connections)
[OK] Table cache hit rate: 30% (287 open / 934 opened)
[OK] Open file limit used: 4% (212/4K)
[!!] Table locks acquired immediately: 84%

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 459.8M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.1.41-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (134M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/1.1G
[OK] Read Key buffer hit rate: 100.0% (269M cached / 74K reads)
[!!] Write Key buffer hit rate: 92.1% (54M cached / 50M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 20.0G/38.3M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/20.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (722453016 hits/ 722453818 total)
[!!] InnoDB Write Log efficiency: 16.11% (57881 hits/ 359336 total)
[OK] InnoDB log waits: 0.00% (0 waits / 301455 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 93.9% (306K cached / 18K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Reduce or eliminate unclosed connections and network issues
    Adjust your join queries to always utilize indexes
    Optimize queries and/or use InnoDB to reduce lock wait
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 4M, or use smaller result sets)
    join_buffer_size (> 512.0K, or always use indexes with joins)
    innodb_buffer_pool_instances(=20)

All status variables: Red fields

Created tmp disk 7.6 k  

Handler read 29.5 k 

Handler read rnd 47.9 M 

Innodb buffer pool 652  

Opened tables   556 

Select full join    13.2 k  

Table locks waited  396.9 k

A client is running a php file.
INSERT INTO t (…) VALUES (…), (…), (…)
Other clients can run the php file at the same time. For example, there are 100 clients. The php file is running repeatedly for each client.
For example, we have 3 clients running at the same time.

1. The php file works for the client.

    INSERT INTO t (...) VALUES (1), (..4.), (.'Sds' ..);

2. The php file works for the client.

    INSERT INTO t (...) VALUES (.22.), (.2 ..), (.'qwe '..);

3. The php file works for the client.
    INSERT INTO t (...) VALUES (.48.), (.6 ..), (.'ty '..);

Currently, there are 120 clients and the CPU goes up to 70% instantly and back to 6%.

UPDATED 2: I think it's 70% because of this query.

BEGIN
SELECT ss.car_route from persons p
inner join person_time ss on ss.comp_id =p.comp_id  
and ss.start_time<=DATE_FORMAT(CURTIME(),'%H:%i')  AND  ss.finish_time>= date_format(curtime() ,'%H:%i') AND ss.car_id=carid
and ss.day_ like concat('%',(select WEEKDAY(now())+1),'%') 
where (p.car_id=carid or p.back_car_id=carid ) LIMIT 1 into @route;
select sf.stop_hiz from car_comp sf 
inner join cars s on s.comp_id = sf.id and s.id=carid
into @hiz_stat;

if @route = 1 THEN

select ss.start_time,ss.finish_time ,p.id,p.name,r.photo, pp.status_ ,pp.time_per,ss.car_route,
ifnull(bh.id,0) AS called,
ifnull(mh.excuse_id,0) AS excuse_id,
ifnull(p.latitude_leave,0) AS latitude_leave,
ifnull(p.longitude_leave,0) AS longitude_leave,
ifnull(p.latitude_take,0) AS latitude_take,
ifnull(p.longitude_take,0) AS longitude_take,
case when (ifnull(p.call_notify,0)=1 or ifnull(p.sms_notify,0)=1) then 1 else 0 end AS call_notify ,
ifnull(p.row_num,0) AS row_num,
ifnull(p.take_phone,0) AS take_phone,
ifnull(p.leave_phone,0) AS leave_phone,
ifnull(p.back_row_num,0) AS back_row_num,
ifnull(ROUND(p.take_notify_meter/2),0) AS take_notify_meter,
ifnull(ROUND(p.leave_notify_meter/2),0) AS leave_notify_meter,
ifnull(p.latitude_notify_take,0) AS latitude_notify_take,
ifnull(p.longitude_notify_take,0) AS longitude_notify_take,
ifnull(p.latitude_notify_leave,0) AS latitude_notify_leave,
ifnull(p.longitude_notify_leave,0) AS longitude_notify_leave,
call_minute,
ifnull(ok.longi,0) as comp_longitude,
ifnull(ok.lati,0) as comp_latitude
from persons p
inner join person_time ss on ss.comp_id =p.comp_id and p.car_id=ss.car_id
and ss.start_time<=DATE_FORMAT(CURTIME(),'%H:%i')  AND  ss.finish_time>= date_format(curtime() ,'%H:%i')
and ss.day_ like concat('%',(select WEEKDAY(now())+1),'%')

LEFT JOIN notify_proc bh ON bh.table_id=p.id AND bh.car_route=@route
and bh.table_name='person' AND bh.notification_=4 AND bh.car_id=p.car_id and date_format(bh.date_,'%Y.%m.%d')=date_format(CURDATE(),'%Y.%m.%d')

left join  person_proc oh   on  pp.person_id=p.id
and pp.car_id=p.car_id
and date_format(pp.time_per,'%H:%i') >=ss.start_time
and date_format(pp.time_per,'%H:%i') <=ss.finish_time
AND pp.car_route= @route
and
pp.id in(select max(id) from person_procs
where date_format(time_per,'%Y.%m.%d')=date_format(CURDATE(),'%Y.%m.%d') and car_id = carid
GROUP by person_id
)
left join comp_way ok on p.comp_id = ok.id and p.car_id=carid
left join excuse_proc ep on mh.person_id=p.id and date_format(mh.time_per,'%Y.%m.%d') = date_format(CURDATE(),'%Y.%m.%d') and (mh.car_route=ss.car_route)
left join photo p on r.person_id = p.id and r.table_name = 'person'
where
(ss.car_route=p.car_u_route or p.car_u_route=3) and

p.car_id = carid and p.active_=1
AND p.work_time=ss.work_time
and @hiz_stat = 0;


else

select ss.start_time,ss.finish_time ,p.id,p.name,r.photo, pp.status_ ,pp.time_per,ss.car_route,
ifnull(bh.id,0) AS called,
ifnull(mh.excuse_id,0) AS excuse_id,
ifnull(p.latitude_leave,0) AS latitude_leave,
ifnull(p.longitude_leave,0) AS longitude_leave,
ifnull(p.latitude_take,0) AS latitude_take,
ifnull(p.longitude_take,0) AS longitude_take,
case when (ifnull(p.call_notify,0)=1 or ifnull(p.sms_notify,0)=1) then 1 else 0 end AS call_notify ,
ifnull(p.row_num,0) AS row_num,
ifnull(p.take_phone,0) AS take_phone,
ifnull(p.leave_phone,0) AS leave_phone,
ifnull(p.back_row_num,0) AS back_row_num,
ifnull(ROUND(p.take_notify_meter/2),0) AS take_notify_meter,
ifnull(ROUND(p.leave_notify_meter/2),0) AS leave_notify_meter,
ifnull(p.latitude_notify_take,0) AS latitude_notify_take,
ifnull(p.longitude_notify_take,0) AS longitude_notify_take,
ifnull(p.latitude_notify_leave,0) AS latitude_notify_leave,
ifnull(p.longitude_notify_leave,0) AS longitude_notify_leave,
call_minute,
ifnull(ok.longi,0) as comp_longitude,
ifnull(ok.lati,0) as comp_latitude
from persons p
inner join person_time ss on ss.comp_id =p.comp_id and p.back_car_id=ss.car_id
and ss.start_time<=DATE_FORMAT(CURTIME(),'%H:%i')  AND  ss.finish_time>= date_format(curtime() ,'%H:%i')
and ss.day_ like concat('%',(select WEEKDAY(now())+1),'%')
LEFT JOIN notify_proc bh ON bh.table_id=p.id AND bh.car_route=@route
and bh.table_name='person' AND bh.notification_=4 AND bh.car_id=p.back_car_id and date_format(bh.date_,'%Y.%m.%d')=date_format(CURDATE(),'%Y.%m.%d')
left join  person_proc oh   on  pp.person_id=p.id
and pp.car_id=p.back_car_id and pp.car_route=2
and date_format(pp.time_per,'%H:%i') >=ss.start_time
and date_format(pp.time_per,'%H:%i') <=ss.finish_time
AND pp.car_route= @route
and
pp.id in(select max(id) from person_procs
where date_format(time_per,'%Y.%m.%d')=date_format(CURDATE(),'%Y.%m.%d') and car_id = carid
GROUP by person_id
)
left join comp_way ok on p.comp_id = ok.id and p.car_id=carid
left join excuse_proc ep on mh.person_id=p.id and date_format(mh.time_per,'%Y.%m.%d') = date_format(CURDATE(),'%Y.%m.%d') and (mh.car_route=ss.car_route)
left join photo p on r.person_id = p.id and r.table_name = 'person'
where
(ss.car_route=p.car_u_route or p.car_u_route=3) and

p.back_car_id = carid and p.active_=1
AND p.work_time=ss.work_time
and @hiz_stat = 0;


END IF;

end

EDIT2:

Best Answer

Suggestions to consider for your my.cnf [mysqld] section

max_connections=200  # from 1500 since max_used_connections was 18 to conserve RAM
thread_cache_size - REMOVE to allow default calc by MySQL and reduce threads_created
innodb_buffer_pool_instances=8  # from 20 to reduce CPU cycles used for IBP mgmt
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
log_error=/var/log/mariadb/mysql_error.log  # from /var/log/mysql_error.log and it may work for you and avoid error message on MySQLTuner.pl report.

Disclaimer: I am the content author of website mentioned in my profile, Network profile where we offer FREE downloadable Utility Scripts, other services and contact information.

Related Solutions

Mysql – Perl – MySQL/MariaDB – slow with no identifiable bottleneck

Conclusion and a workaround

After exhausting all options on Windows, I decided to switch to Linux, mostly because I was frustrated with inability to profile and debug in detail.

I have moved the whole setup to Ubuntu 14.04. I first tried XAMPP but gave up because of conflicts between XAMPP and MySQL and MySQL Workbench. Then I moved to vanilla MySQL (5.5, I think) and vanilla Apache 2.

However, I was still left with the same problem – no visible bottleneck and resources still underutilized. I suspected throttling in TCP sockets (used between Perl code and MySQL), but further profiling proved this not to be the case.

Then, I turned my attantion to Perl DBI module DBD::SQL, thinking that it may be doing some throttlinig. I did some tests where I replaced DBI calls in Perl with system calls (system("mysql -e'INSERT INTO blah blah …'). I have determined that the performance did not change, therefore absolving DBI as a culprit.

I need to add one important detail now: I was in fact always running a number of my Perl scripts in parallel. Given that the CPU has 8 cores, this is necessary to utilize all of them, of course. Further debugging showed that almost all my perl processes which were supposed to work furiously were sleeping most of the time. Ubunty System Monitor shawed them as waiting on Waiting Channels wait_answer_interruptible or unix_stream_recvmsg. CPU History graph in System Monitor showed all perl processes jumping to 100% CPU utilization and then dropping to ~0% in unison. I suspected that MySQL server is not configured for multi threading, but htop showed 17 mysqld threads activated, confirming that all should be ok.
I suspected that all MySQL threads were waiting on the same semaphore and were locked out for most of the time. I dreaded delving into the dark bowels of MySQL trying to figure out what goes on inside. Instead, I decided to replace MySQL with MariaDB, even though MariaDB seems to have had the same issue originally when I was running it on Windows.

Lo and behold – this finally worked. My perl scripts were screaming.

One last problem remained: I had a very rudimentary method of parallelising the perl scripts: I would just run 10 or 20 with their respective loads and hope that they would utilize all the resources.

This has obvious drawbacks: if too many processes are spawned, the OS may spend too much time swapping them (although not a serious issue with only 20 processes, it becomes an issue with e.g. 1000). If not enough processes are spawned (e.g. less than 8, for each core) the CPU will not be utilized fully for sure. If too many processes exhaust RAM, Linux will turn to disk and will start swapping. As soon as this starts happening, everything grinds to a halt.

I searched but could not find a perl library/script/code which would spawn new processes only when CPU, memory and disk are under utilized. Hence I created my own: raspawn.pl (resource aware spawn) which I placed on github. Raspawn.pl spawns a number of processes while trying to keep resources utilization just below the maximum. It constantly checks the CPU, memory and disk utilization and only if all are less than ~90% utilized, starts a new process.

Finally, this worked. I can now process my whole load in around 7 days, instead of many months...

Mysql Optimization suggessions

Observations:

Version: 5.5.41-0ubuntu0.14.04.1
64 GB of RAM
Uptime = 3d 03:40:44
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

Increase innodb_buffer_pool_size to at least 1G (not more than 45G). Sounds like most of your 64GB is unused? Or do you expect a huge growth in data?

Turn off the Query Cache; it does not seem to be useful:

query_cache_size = 0
query_cache_type = OFF

Set long_query_time = 2 and turn on the slowlog. After a day, run pt-query-digest to find the worst queries. Then work on optimizing them. (Composite indexes, reformulating queries, etc. Ask for help if needed.)

Supporting details and other suggestions

( innodb_buffer_pool_size / _ram ) = 128M / 65536M = 0.20% -- % of RAM used for InnoDB buffer_pool ( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 128M / 0.70) / 65536M = 0.40% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

[!!] InnoDB buffer pool / data size: 128.0M/873.0M
So innodb_buffer_pool_size should be at least 1G.

[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
0 out of 1 -- bogus to mark it "!!"

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 2,068,247 / 5340758 = 38.7% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size

( innodb_file_per_table ) = OFF -- Put each file in its own tablespace -- (Mildly recommended, especially for large tables)

( Qcache_hits / Qcache_inserts ) = 1,765,289 / 2,509,742 = 0.703 -- Hit to insert ratio -- high is good -- Consider turning off the query cache.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 14960424) / 1477 / 8192 = 0.15 -- query_alloc_block_size vs formula -- Adjust query_alloc_block_size

[!!] Query cache prunes per day: 15154

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 3,090 / (3090 + 4930) = 38.5% -- Percent of temp tables that spilled to disk -- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Select_scan ) = 459,980 / 272444 = 1.7 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 459,980 / 2518409 = 18.3% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( binlog_format ) = STATEMENT -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( log_slow_queries ) = OFF -- Whether to log slow queries. (Before 5.1.29, 5.6.1)

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( Connections ) = 2,305,560 / 272,444 = 8.5 /sec -- Connections -- Increase wait_timeout; use pooling?

( Max_used_connections ) = 66 -- How many simultaneous connections you had (highwater mark).
[!!] Highest connection usage: 100% (152/151)
Those disagree; perhaps you restarted?

( open_files_limit ) = 1,024 -- ulimit -n -- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent) -- If you get more tables, raising this will be important.

( Opened_tables ) = 43/hour
[!!] Table cache hit rate: 6% (400 open / 5K opened)
These disagree; perhaps there is no problem here.

[!!] Key buffer used: 18.3% (3M used / 16M cache)
[!!] Read Key buffer hit rate: 91.7% (743 cached / 62 reads)
Not a problem since there is virtually no MyISAM usage and key_buffer_size is only a tiny percentage of RAM.

19 issues flagged, out of 133 computed Variables/Status/Expressions

My take on Tuner's recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance -- waste of time!
Enable the slow query log to troubleshoot bad queries -- yes
Reduce or eliminate persistent connections to reduce connection usage -- ok
Adjust your join queries to always utilize indexes -- yes (use slowlog to find them)
When making adjustments, make tmp_table_size/max_heap_table_size equal -- ok
Reduce your SELECT DISTINCT queries which have no LIMIT clause -- yawn
Increase table_open_cache gradually to avoid file descriptor limits --
    ok, but may not be critical
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable -- noted
should be greater than table_open_cache ( 400) -- it is

max_connections (> 151) -- No; figure out why there are so many connections
wait_timeout ( 16M) -- probably not important
join_buffer_size (> 128.0K, or always use indexes with joins) -- might help, might not
tmp_table_size (> 16M) -- first look for other ways to improve queries
max_heap_table_size (> 16M)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 872M) if possible. -- yes

Best Answer

Related Solutions

Mysql – Perl – MySQL/MariaDB – slow with no identifiable bottleneck

Mysql Optimization suggessions

Related Question